using System;
using System.Data;
using System.Data.SqlClient;

using EntLibEC.Cmn;
using EntLibEC.Info.Basic;

namespace EntLibEC.Dac.Basic
{
	/// <summary>
	/// Summary description for CategoryDac.
	/// </summary>
	public class CategoryDac
	{
		
		public CategoryDac()
		{
		}

		public int Insert(Category1Info oParam)
		{
			string sql = @"INSERT INTO Ent_Category1
                            (
                            SysNo, C1ID, C1Name, Status
                            )
                            VALUES (
                            @SysNo, @C1ID, @C1Name, @Status
                            )";
			SqlCommand cmd = new SqlCommand(sql);

            SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
			SqlParameter paramC1ID = new SqlParameter("@C1ID", SqlDbType.NVarChar,20);
			SqlParameter paramC1Name = new SqlParameter("@C1Name", SqlDbType.NVarChar,200);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);

            paramSysNo.Value = oParam.SysNo;
			paramC1ID.Value = oParam.C1ID;
			paramC1Name.Value = oParam.C1Name;
			paramStatus.Value = oParam.Status;

            cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramC1ID);
			cmd.Parameters.Add(paramC1Name);
			cmd.Parameters.Add(paramStatus);

			return SqlHelper.ExecuteNonQuery(cmd);
		}
		public int Insert(Category2Info oParam)
		{
			string sql = @"INSERT INTO Ent_Category2
                            (
                            C1SysNo, C2ID, C2Name, 
                            Status
                            )
                            VALUES (
                            @C1SysNo, @C2ID, @C2Name, 
                            @Status
                            );";
            sql += "set @SysNo = SCOPE_IDENTITY()";
			SqlCommand cmd = new SqlCommand(sql);

            SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
            paramSysNo.Direction = ParameterDirection.Output;

			SqlParameter paramC1SysNo = new SqlParameter("@C1SysNo", SqlDbType.Int,4);
			SqlParameter paramC2ID = new SqlParameter("@C2ID", SqlDbType.NVarChar,20);
			SqlParameter paramC2Name = new SqlParameter("@C2Name", SqlDbType.NVarChar,200);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);

			paramC1SysNo.Value = oParam.C1SysNo;
			paramC2ID.Value = oParam.C2ID;
			paramC2Name.Value = oParam.C2Name;
			paramStatus.Value = oParam.Status;

            cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramC1SysNo);
			cmd.Parameters.Add(paramC2ID);
			cmd.Parameters.Add(paramC2Name);
			cmd.Parameters.Add(paramStatus);

            int sysno = AppConst.IntNull;
            SqlHelper.ExecuteNonQuery(cmd, out sysno);
            oParam.SysNo = sysno;

            return sysno;
		}

		public int Insert(Category3Info oParam)
		{
			string sql = @"INSERT INTO Ent_Category3
                            (
                            C2SysNo, C3ID, C3Name, 
                            Status, SearchByAttribute, C3Bulletin
                            )
                            VALUES (
                            @C2SysNo, @C3ID, @C3Name, 
                            @Status , @SearchByAttribute, @C3Bulletin
                            )";
            sql += "set @SysNo = SCOPE_IDENTITY()";
			SqlCommand cmd = new SqlCommand(sql);

            SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
            paramSysNo.Direction = ParameterDirection.Output;

			SqlParameter paramC2SysNo = new SqlParameter("@C2SysNo", SqlDbType.Int,4);
			SqlParameter paramC3ID = new SqlParameter("@C3ID", SqlDbType.NVarChar,20);
			SqlParameter paramC3Name = new SqlParameter("@C3Name", SqlDbType.NVarChar,200);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
			SqlParameter paramSearchByAttribute = new SqlParameter("@SearchByAttribute", SqlDbType.Int,4);
			SqlParameter paramC3Bulletin = new SqlParameter("@C3Bulletin", SqlDbType.NVarChar,200);

			paramC2SysNo.Value = oParam.C2SysNo;
			paramC3ID.Value = oParam.C3ID;
			paramC3Name.Value = oParam.C3Name;
			paramStatus.Value = oParam.Status;
			paramSearchByAttribute.Value = oParam.SearchByAttribute;
			paramC3Bulletin.Value = oParam.C3Bulletin;
            
            cmd.Parameters.Add(paramSysNo);
            cmd.Parameters.Add(paramC2SysNo);
			cmd.Parameters.Add(paramC3ID);
			cmd.Parameters.Add(paramC3Name);
			cmd.Parameters.Add(paramStatus);
			cmd.Parameters.Add(paramSearchByAttribute);
			cmd.Parameters.Add(paramC3Bulletin);

            int sysno = AppConst.IntNull;
            SqlHelper.ExecuteNonQuery(cmd, out sysno);
            oParam.SysNo = sysno;

            return sysno;
        }
		public int Update(Category1Info oParam)
		{
			string sql = @"UPDATE Ent_Category1 SET 
                            C1ID=@C1ID, 
                            C1Name=@C1Name, Status=@Status
                            WHERE SysNo=@SysNo";
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramC1ID = new SqlParameter("@C1ID", SqlDbType.NVarChar,20);
			SqlParameter paramC1Name = new SqlParameter("@C1Name", SqlDbType.NVarChar,200);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);

			paramSysNo.Value = oParam.SysNo;
			paramC1ID.Value = oParam.C1ID;
			paramC1Name.Value = oParam.C1Name;
			paramStatus.Value = oParam.Status;

			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramC1ID);
			cmd.Parameters.Add(paramC1Name);
			cmd.Parameters.Add(paramStatus);

			return SqlHelper.ExecuteNonQuery(cmd);
		}
		public int Update(Category2Info oParam)
		{
			string sql = @"UPDATE Ent_Category2 SET 
                            C1SysNo=@C1SysNo, 
                            C2ID=@C2ID, C2Name=@C2Name, 
                            Status=@Status
                            WHERE SysNo=@SysNo";
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramC1SysNo = new SqlParameter("@C1SysNo", SqlDbType.Int,4);
			SqlParameter paramC2ID = new SqlParameter("@C2ID", SqlDbType.NVarChar,20);
			SqlParameter paramC2Name = new SqlParameter("@C2Name", SqlDbType.NVarChar,200);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);

			paramSysNo.Value = oParam.SysNo;
			paramC1SysNo.Value = oParam.C1SysNo;
			paramC2ID.Value = oParam.C2ID;
			paramC2Name.Value = oParam.C2Name;
			paramStatus.Value = oParam.Status;

			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramC1SysNo);
			cmd.Parameters.Add(paramC2ID);
			cmd.Parameters.Add(paramC2Name);
			cmd.Parameters.Add(paramStatus);

			return SqlHelper.ExecuteNonQuery(cmd);
		}
		public int Update(Category3Info oParam)
		{
			string sql = @"UPDATE Ent_Category3 SET 
                            C2SysNo=@C2SysNo, 
                            C3ID=@C3ID, C3Name=@C3Name, 
                            Status=@Status, SearchByAttribute = @SearchByAttribute, C3Bulletin = @C3Bulletin
                            WHERE SysNo=@SysNo";
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramC2SysNo = new SqlParameter("@C2SysNo", SqlDbType.Int,4);
			SqlParameter paramC3ID = new SqlParameter("@C3ID", SqlDbType.NVarChar,20);
			SqlParameter paramC3Name = new SqlParameter("@C3Name", SqlDbType.NVarChar,200);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);
			SqlParameter paramSearchByAttribute = new SqlParameter("@SearchByAttribute", SqlDbType.Int,4);
			SqlParameter paramC3Bulletin = new SqlParameter("@C3Bulletin", SqlDbType.NVarChar,200);

			paramSysNo.Value = oParam.SysNo;
			paramC2SysNo.Value = oParam.C2SysNo;
			paramC3ID.Value = oParam.C3ID;
			paramC3Name.Value = oParam.C3Name;
			paramStatus.Value = oParam.Status;
			paramSearchByAttribute.Value = oParam.SearchByAttribute;
			paramC3Bulletin.Value = oParam.C3Bulletin;

			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramC2SysNo);
			cmd.Parameters.Add(paramC3ID);
			cmd.Parameters.Add(paramC3Name);
			cmd.Parameters.Add(paramStatus);
			cmd.Parameters.Add(paramSearchByAttribute);
			cmd.Parameters.Add(paramC3Bulletin);

			return SqlHelper.ExecuteNonQuery(cmd);
		}
		public int InsertAttribute(CategoryAttributeInfo oParam)
		{
			string sql = @"INSERT INTO Ent_Category_Attribute
                            (
                            C3SysNo, AttributeID, AttributeName, 
                            OrderNum, Status
                            )
                            VALUES (
                            @C3SysNo, @AttributeID, @AttributeName, 
                            @OrderNum, @Status
                            )";
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramC3SysNo = new SqlParameter("@C3SysNo", SqlDbType.Int,4);
			SqlParameter paramAttributeID = new SqlParameter("@AttributeID", SqlDbType.NVarChar,20);
			SqlParameter paramAttributeName = new SqlParameter("@AttributeName", SqlDbType.NVarChar,50);
			SqlParameter paramOrderNum = new SqlParameter("@OrderNum", SqlDbType.Int,4);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);

			paramC3SysNo.Value = oParam.C3SysNo;
			paramAttributeID.Value = oParam.AttributeID;
			paramAttributeName.Value = oParam.AttributeName;
			paramOrderNum.Value = oParam.OrderNum;
			paramStatus.Value = oParam.Status;

			cmd.Parameters.Add(paramC3SysNo);
			cmd.Parameters.Add(paramAttributeID);
			cmd.Parameters.Add(paramAttributeName);
			cmd.Parameters.Add(paramOrderNum);
			cmd.Parameters.Add(paramStatus);

			return SqlHelper.ExecuteNonQuery(cmd);
		}
		public int UpdateAttribute(CategoryAttributeInfo oParam)
		{
			string sql = @"UPDATE Ent_Category_Attribute SET 
                            C3SysNo=@C3SysNo, 
                            AttributeID=@AttributeID, AttributeName=@AttributeName, 
                            OrderNum=@OrderNum, Status=@Status
                            WHERE SysNo=@SysNo";
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramC3SysNo = new SqlParameter("@C3SysNo", SqlDbType.Int,4);
			SqlParameter paramAttributeID = new SqlParameter("@AttributeID", SqlDbType.NVarChar,20);
			SqlParameter paramAttributeName = new SqlParameter("@AttributeName", SqlDbType.NVarChar,50);
			SqlParameter paramOrderNum = new SqlParameter("@OrderNum", SqlDbType.Int,4);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);

			paramSysNo.Value = oParam.SysNo;
			paramC3SysNo.Value = oParam.C3SysNo;
			paramAttributeID.Value = oParam.AttributeID;
			paramAttributeName.Value = oParam.AttributeName;
			paramOrderNum.Value = oParam.OrderNum;
			paramStatus.Value = oParam.Status;

			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramC3SysNo);
			cmd.Parameters.Add(paramAttributeID);
			cmd.Parameters.Add(paramAttributeName);
			cmd.Parameters.Add(paramOrderNum);
			cmd.Parameters.Add(paramStatus);

			return SqlHelper.ExecuteNonQuery(cmd);
		}
		public int UpdateAttributeByC3andAID(CategoryAttributeInfo oParam)
		{
			string sql = @"UPDATE Ent_Category_Attribute SET 
                             AttributeName=@AttributeName, 
                            OrderNum=@OrderNum, Status=@Status
                            WHERE C3SysNo=@C3SysNo and AttributeID=@AttributeID ";
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramC3SysNo = new SqlParameter("@C3SysNo", SqlDbType.Int,4);
			SqlParameter paramAttributeID = new SqlParameter("@AttributeID", SqlDbType.NVarChar,20);
			SqlParameter paramAttributeName = new SqlParameter("@AttributeName", SqlDbType.NVarChar,50);
			SqlParameter paramOrderNum = new SqlParameter("@OrderNum", SqlDbType.Int,4);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int,4);

			paramC3SysNo.Value = oParam.C3SysNo;
			paramAttributeID.Value = oParam.AttributeID;
			paramAttributeName.Value = oParam.AttributeName;
			paramOrderNum.Value = oParam.OrderNum;
			paramStatus.Value = oParam.Status;

			cmd.Parameters.Add(paramC3SysNo);
			cmd.Parameters.Add(paramAttributeID);
			cmd.Parameters.Add(paramAttributeName);
			cmd.Parameters.Add(paramOrderNum);
			cmd.Parameters.Add(paramStatus);

			return SqlHelper.ExecuteNonQuery(cmd);
		}
		public int SetOrderNum(CategoryAttributeInfo oParam)
		{
			string sql = "update Ent_Category_Attribute set ordernum = " + oParam.OrderNum + " where sysno = " + oParam.SysNo;
			SqlCommand cmd = new SqlCommand(sql);
			return SqlHelper.ExecuteNonQuery(cmd);
		}

		public int UpdateCategory3PM(int c3sysno ,int pmsysno)
		{
			string sql = "update Ent_Product set PMUserSysNo =" + pmsysno				         
				+ " where C3SysNo = " + c3sysno ;

			return SqlHelper.ExecuteNonQuery(sql);
		}

		public int SetMaxPerOrderByCategory(int c3sysno, int maxPerOrder)
		{
			string sql = "update Ent_Product_Price set maxPerOrder =" + maxPerOrder	
				+ " from Ent_Product where Ent_Product.sysno = Ent_Product_Price.productSysNo and C3SysNo = " + c3sysno ;

			return SqlHelper.ExecuteNonQuery(sql);

		}
	}
}
